package com.videoshare.user.dao;

import org.apache.commons.logging.*;
import com.sapientia.user.UserRegistrationForm;
import com.sapientia.user.*;
import com.sapientia.user.bean.*;
import java.sql.*;
import javax.sql.*;
import java.util.*;

public class UserDAOImpl extends BaseDAO implements UserDAO
{
	private static Log log = LogFactory.getLog(UserDAOImpl.class);
	
	/** datasource */
    private DataSource dataSource = null;
    
    /** connection */
    private Connection conn = null;

    /** costruttore, utilizza il datasource passato al costruttore come argomento */
    public UserDAOImpl(DataSource dataSource)
    {
        this.dataSource = dataSource;
    }
    
    /** costruttore, utilizza la connessione passata al costruttore come argomento */
    public UserDAOImpl(Connection conn)
    {
        this.conn = conn;
    }
    
    public List getUser(String email)
    {
    	User user = null;
    	
    	PreparedStatement pstmt = null;
        ResultSet rset = null;
        List users = null;
        
        
        try {
            if( conn == null )
                conn = dataSource.getConnection();

            
            DatabaseMetaData dbmd = conn.getMetaData();
            
            
            int transactionIsolation = dbmd.getDefaultTransactionIsolation();
            String dbProductName = dbmd.getDatabaseProductName();
            String dbVersion = dbmd.getDatabaseProductVersion();
            String driverName = dbmd.getDriverName();
            String url = dbmd.getURL();
            String info = "Connect to the db " + dbProductName + " " +
			            " DB Version: " + dbVersion + 
			            " Driver: " + driverName +
			            " URL: " + url +
			            " Transaction Isolation: " + transactionIsolation;
            
            System.out.println(info);
            
            log.info(info);
            
            
            String query = "SELECT firstname, lastname, username, password, registered, profile, phone  FROM user WHERE email='" + email + "'";
            
            pstmt = conn.prepareStatement(query);
            
            rset = pstmt.executeQuery();
            
            users = makeBeanList(resultsetToList(rset));
            
            
            log.info("Got user data from the database.");
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
            log.error("Cannot get data from the database");
        }
        finally {
            
            try 
            { 
                if( rset != null )
                    rset.close(); 
                
            }
            catch(Exception e) {
                System.out.println("Class UserDAO, getAllUsers() method call error..");
                e.printStackTrace();
                log.error("Cannot close the result set");
            }
            
            try 
            { 
                if( pstmt != null )
                    pstmt.close(); 
                
            }
            catch(Exception e) { 
                System.out.println("Class UserDAO, getAllUsers() method call error..");
                e.printStackTrace();
                log.error("Cannot close the prepared statement.");
            }
            
        }
        
        return users;

    }
    
    public List getRegisteredUsers(boolean enabled)
    {
    	User user = null;
    	
    	PreparedStatement pstmt = null;
        ResultSet rset = null;
        List users = null;
        
        int registered = 0;
        
        if(enabled)
        	registered = 1;
        
        try {
            if( conn == null )
                conn = dataSource.getConnection();

            
            DatabaseMetaData dbmd = conn.getMetaData();
            
            
            int transactionIsolation = dbmd.getDefaultTransactionIsolation();
            String dbProductName = dbmd.getDatabaseProductName();
            String dbVersion = dbmd.getDatabaseProductVersion();
            String driverName = dbmd.getDriverName();
            String url = dbmd.getURL();
            String info = "Connect to the db " + dbProductName + " " +
			            " DB Version: " + dbVersion + 
			            " Driver: " + driverName +
			            " URL: " + url +
			            " Transaction Isolation: " + transactionIsolation;
            
            System.out.println(info);
            
            log.info(info);
            
            
            String query = "SELECT firstname, lastname, email, registered FROM user WHERE registered='" + registered + "'";
            
            pstmt = conn.prepareStatement(query);
            
            rset = pstmt.executeQuery();
            
            users = makeBeanList(resultsetToList(rset));
            
            
            log.info("Got user data from the database.");
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
            log.error("Cannot get data from the database");
        }
        finally {
            
            try 
            { 
                if( rset != null )
                    rset.close(); 
                
            }
            catch(Exception e) {
                System.out.println("Class UserDAO, getAllUsers() method call error..");
                e.printStackTrace();
                log.error("Cannot close the result set");
            }
            
            try 
            { 
                if( pstmt != null )
                    pstmt.close(); 
                
            }
            catch(Exception e) { 
                System.out.println("Class UserDAO, getAllUsers() method call error..");
                e.printStackTrace();
                log.error("Cannot close the prepared statement.");
            }
            
        }
        
        return users;

    }

    
    
    public int updateUserRegistered(String email, int registered)
    {
    	PreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        
        int rows = 0;
        
        String statement = "UPDATE user SET  registered='" + registered + "' WHERE email='" + email + "'";
        								   

		System.out.println("Inserting a new user in local db: " + statement);
		
		try 
		{
			if( conn == null )
                conn = dataSource.getConnection();
			
			pstmt = conn.prepareStatement( statement );
			
			rows = pstmt.executeUpdate();
			
			System.out.println("Rows affected: " + rows);
		
		}
		
		catch(SQLException sqle){
			System.out.println("Connection failed.");
			sqle.printStackTrace();
			
		}
		
		finally {
		if(rs != null){
		    try {
		        rs.close();
		    }
		    catch(SQLException sqle){
		        System.out.println("result set close failed");
		    }
		}
		if(pstmt != null){
		    try {
		        pstmt.close();
		   
		    }
		    catch(SQLException sqle){
		        System.out.println("prepared statement close failed");
		    }
		}
		
		}
		return rows;
    }

    
    public void createUser(User user)
    {
    	PreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        
        int rows = 0;
        
        String statement = "INSERT INTO user (EMAIL,FIRSTNAME,LASTNAME,PASSWORD,PHONE)" + 
        					"VALUES ('" +  user.getEmail() + "','" + 
        								   user.getFirstName() + "','" +
        								   user.getLastName() + "','" +
        								   user.getPassword() + "','" +
        								   user.getPhone() + "')";
        								   

		System.out.println("Inserting a new user in local db: " + statement);
		
		try 
		{
			if( conn == null )
                conn = dataSource.getConnection();
			
			pstmt = conn.prepareStatement( statement );
			
			rows = pstmt.executeUpdate();
			
			System.out.println("Rows affected: " + rows);
		
		}
		
		catch(SQLException sqle){
			System.out.println("Connection failed.");
			sqle.printStackTrace();
			
		}
		
		finally {
		if(rs != null){
		    try {
		        rs.close();
		    }
		    catch(SQLException sqle){
		        System.out.println("result set close failed");
		    }
		}
		if(pstmt != null){
		    try {
		        pstmt.close();
		   
		    }
		    catch(SQLException sqle){
		        System.out.println("prepared statement close failed");
		    }
		}
		
		}
		
    	
    }
    
    public List listUsers()
    {
    	PreparedStatement pstmt = null;
        ResultSet rset = null;
        List users = null;
        
        
        try {
            if( conn == null )
                conn = dataSource.getConnection();

            
            DatabaseMetaData dbmd = conn.getMetaData();
            
            
            int transactionIsolation = dbmd.getDefaultTransactionIsolation();
            String dbProductName = dbmd.getDatabaseProductName();
            String dbVersion = dbmd.getDatabaseProductVersion();
            String driverName = dbmd.getDriverName();
            String url = dbmd.getURL();
            String info = "Connect to the db " + dbProductName + " " +
			            " DB Version: " + dbVersion + 
			            " Driver: " + driverName +
			            " URL: " + url +
			            " Transaction Isolation: " + transactionIsolation;
            
            System.out.println(info);
            
            log.info(info);
            
            
            String query = "SELECT * FROM user";
            
            pstmt = conn.prepareStatement(query);
            
            rset = pstmt.executeQuery();
            
            users = makeBeanList(resultsetToList(rset));
            
            
            log.info("Got users data from the database.");
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
            log.error("Cannot get data from the database");
        }
        finally {
            
            try 
            { 
                if( rset != null )
                    rset.close(); 
                
            }
            catch(Exception e) {
                System.out.println("Class UserDAO, getAllUsers() method call error..");
                e.printStackTrace();
                log.error("Cannot close the result set");
            }
            
            try 
            { 
                if( pstmt != null )
                    pstmt.close(); 
                
            }
            catch(Exception e) { 
                System.out.println("Class UserDAO, getAllUsers() method call error..");
                e.printStackTrace();
                log.error("Cannot close the prepared statement.");
            }
            
        }
        
        return users;

    }

	public int insertUser(UserRegistrationForm userForm) throws SQLException
	{
		PreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        
        int rows = 0;
        
        String statement = "INSERT INTO user (EMAIL,FIRSTNAME,LASTNAME,USERNAME,PASSWORD,REGISTERED,REG_DATE,END_DATE,PROFILE,PHONE)" + 
        					"VALUES ('" +  userForm.getEmail() + "','" + 
        								   userForm.getFirstName() + "','" +
        								   userForm.getLastName() + "','" +
        								   userForm.getUserName() + "','" +
        								   userForm.getPassword() + "','" +
        								   userForm.isRegistered() + "','" +
        								   userForm.getRegDate() + "','" +
        								   userForm.getEndDate() + "','" +
        								   userForm.getProfile() + "','" +
        								   userForm.getPhone() + "')";
        								   

		System.out.println("Inserting a new user in local db: " + statement);
		
		try 
		{
			if( conn == null )
                conn = dataSource.getConnection();
			
			pstmt = conn.prepareStatement( statement );
			
			rows = pstmt.executeUpdate();
			
			System.out.println("Rows affected: " + rows);
		
		}
		/*
		catch(SQLException sqle){
			System.out.println("Connection failed.");
			sqle.printStackTrace();
			
		}
		*/
		finally {
		if(rs != null){
		    try {
		        rs.close();
		    }
		    catch(SQLException sqle){
		        System.out.println("result set close failed");
		    }
		}
		if(pstmt != null){
		    try {
		        pstmt.close();
		   
		    }
		    catch(SQLException sqle){
		        System.out.println("prepared statement close failed");
		    }
		}
		
		}
		
		return rows;

		
	}
	
	/**
	 * Restituisce un oggetto List contenente bean di tipo User
	 * @return List 
	 * @throws SQLException
	 */
	public List getAllUsers() throws SQLException
	{
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        List users = null;
        
        
        try {
            if( conn == null )
                conn = dataSource.getConnection();

            
            DatabaseMetaData dbmd = conn.getMetaData();
            
            
            int transactionIsolation = dbmd.getDefaultTransactionIsolation();
            String dbProductName = dbmd.getDatabaseProductName();
            String dbVersion = dbmd.getDatabaseProductVersion();
            String driverName = dbmd.getDriverName();
            String url = dbmd.getURL();
            String info = "Connect to the db " + dbProductName + " " +
			            " DB Version: " + dbVersion + 
			            " Driver: " + driverName +
			            " URL: " + url +
			            " Transaction Isolation: " + transactionIsolation;
            
            System.out.println(info);
            
            log.info(info);
            
            
            String query = "SELECT * FROM user";
            
            pstmt = conn.prepareStatement(query);
            
            rset = pstmt.executeQuery();
            
            users = makeBeanList(resultsetToList(rset));
            
            
            log.info("Got users data from the database.");
            
        } 
        /*
        catch(SQLException e) {
            e.printStackTrace();
            log.error("Cannot get data from the database");
        }
        */
        finally {
            
            try 
            { 
                if( rset != null )
                    rset.close(); 
                
            }
            catch(Exception e) {
                System.out.println("Class UserDAO, getAllUsers() method call error..");
                e.printStackTrace();
                log.error("Cannot close the result set");
            }
            
            try 
            { 
                if( pstmt != null )
                    pstmt.close(); 
                
            }
            catch(Exception e) { 
                System.out.println("Class UserDAO, getAllUsers() method call error..");
                e.printStackTrace();
                log.error("Cannot close the prepared statement.");
            }
            
        }
        
        return users;

	}
	
	public boolean checkUser(UserLoginForm userForm) throws SQLException
	{
		boolean isUser = false;
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        List userList = null;
        
        try {
            if( conn == null )
                conn = dataSource.getConnection();

            
            DatabaseMetaData dbmd = conn.getMetaData();
            
            
            int transactionIsolation = dbmd.getDefaultTransactionIsolation();
            String dbProductName = dbmd.getDatabaseProductName();
            String dbVersion = dbmd.getDatabaseProductVersion();
            String driverName = dbmd.getDriverName();
            String url = dbmd.getURL();
            String info = "Connect to the db " + dbProductName + " " +
			            " DB Version: " + dbVersion + 
			            " Driver: " + driverName +
			            " URL: " + url +
			            " Transaction Isolation: " + transactionIsolation;
            
            System.out.println(info);
            
            log.info(info);
            
            
            String query = "SELECT username, password, profile, email FROM user " +
            				"WHERE username='" + userForm.getUserName().trim() + "' " +
            				"AND password='" + userForm.getPassword().trim() + "'";
            
            System.out.println("Check user: " + query);
            
            pstmt = conn.prepareStatement(query);
            
            rset = pstmt.executeQuery();
            
            
            userList = makeBeanList(resultsetToList(rset));
            
            if( userList.size() > 0)
            	isUser = true;
            
            Iterator iterator = userList.iterator();
            while(iterator.hasNext())
            {
            	User user = (User) iterator.next();
            	userForm.setProfile(user.getProfile());
            }
            
            log.info("Got users data from the database.");
            
        } 
        /*
        catch(SQLException e) {
            e.printStackTrace();
            log.error("Cannot get data from the database");
        }
        */
        finally {
            
            try 
            { 
                if( rset != null )
                    rset.close(); 
                
            }
            catch(Exception e) {
                System.out.println("Class UserDAOImpl, getAllUsers() method call error..");
                e.printStackTrace();
                log.error("Cannot close the result set");
            }
            
            try 
            { 
                if( pstmt != null )
                    pstmt.close(); 
                
            }
            catch(Exception e) { 
                System.out.println("Class UserDAOImpl, getAllUsers() method call error..");
                e.printStackTrace();
                log.error("Cannot close the prepared statement.");
            }
            
        }
        
		return isUser;
	}
	
	/**
     * Preleva i dati del result set contenuti in un oggetto List e li salva in un 
     * bean appropriato, facendo corrispondere un bean a ciascun record.
     * Restituisce una lista di bean
     */
    private List makeBeanList(List rows)
    {
        List beanList = new ArrayList();
         
        ListIterator rowIterator = rows.listIterator();
        int rowcounter = 0;
        while(rowIterator.hasNext())
        {
            rowcounter++;
            List fieldlist = (List)rowIterator.next();
            ListIterator fieldsIterator = fieldlist.listIterator();
            Object obj = null;
            String type = "";
            String columnName = "";
            User userBean = new User();
            
            
            while(fieldsIterator.hasNext())
            {
                TableFieldHandler tfhandler = (TableFieldHandler)fieldsIterator.next();
                obj = tfhandler.getObject();
                type = tfhandler.getType();
                columnName = tfhandler.getColumnName().trim();
                
                if( columnName.equalsIgnoreCase("EMAIL") )
                {
                    userBean.setEmail( (String)obj );
                    System.out.println("Email: " + userBean.getEmail());
                }
                
                if( columnName.equalsIgnoreCase("FIRSTNAME") )
                {
                    userBean.setFirstName( (String)obj );
                    System.out.println("First Name: " + userBean.getFirstName());
                } 
       
                if( columnName.equalsIgnoreCase("LASTNAME") )
                {
                    userBean.setLastName((String)obj);
                    System.out.println("Last Name: " + userBean.getLastName());
                } 
                
                if( columnName.equalsIgnoreCase("USERNAME") )
                {
                    userBean.setUserName((String)obj);
                    System.out.println("User Name: " + userBean.getUserName());
                } 
                
                if( columnName.equalsIgnoreCase("PHONE") )
                {
                    userBean.setPhone( (String)obj );
                    System.out.println("Phone: " + userBean.getPhone());
                } 
                
                if( columnName.equalsIgnoreCase("PASSWORD") )
                {
                    userBean.setPassword( (String)obj );
                    System.out.println("Password: " + userBean.getPassword());
                } 
                
                if( columnName.equalsIgnoreCase("PROFILE") )
                {
                    userBean.setProfile( (String)obj );
                    System.out.println("Profile: " + userBean.getProfile());
                } 
                
                if( columnName.equalsIgnoreCase("REGISTERED") )
                {
                	                	
                    userBean.setRegistered(Integer.parseInt(obj.toString()));
                    System.out.println("is registered: " + userBean.getRegistered());
                } 
                
                if( columnName.equalsIgnoreCase("REG_DATE") )
                {
                    userBean.setRegDate( (String)obj );
                    System.out.println("Registration date: " + userBean.getRegDate());
                } 
                
                if( columnName.equalsIgnoreCase("END_DATE") )
                {
                    userBean.setEndDate( (String)obj );
                    System.out.println("End date: " + userBean.getEndDate());
                } 
                
                
                
                
                
            }
            
            beanList.add(userBean);
            
         }
        
        return beanList;
        
    }


}
